"""
    pymysql执行DDL语句
"""
from pymysql import Connection

con = None

try:
    # 创建数据库连接
    con = Connection(
        host="localhost",  # 主机名
        port=3306,  # 端口
        user="root",  # 账户
        password="123456",  # 密码
        database="db_python"  # 指定操作的数据库
    )
    # 创建游标对象
    cursor = con.cursor()

    # 定义一个建表sql语句
    sql = """
   `id` int NOT NULL AUTO_INCREMENT,
   `doctor_code` varchar(20) NOT NULL COMMENT '医生代码',
   `doctor_name` varchar(100) NOT NULL COMMENT '医生名称',
   `outpatient_visits` int DEFAULT '0' COMMENT '门诊人次',
   `avg_fee_per_visit` decimal(12,4) DEFAULT '0.0000' COMMENT '均次费用',
   `avg_drug_fee_per_visit` decimal(12,4) DEFAULT '0.0000' COMMENT '均次药品费',
   `avg_inspection_fee_per_visit` decimal(12,4) DEFAULT '0.0000' COMMENT '均次检查费',
   `avg_treatment_fee_per_visit` decimal(12,4) DEFAULT '0.0000' COMMENT '均次治疗费',
   `antibiotic_prescription_rate` decimal(5,2) DEFAULT '0.00' COMMENT '抗菌药处方比例',
   `prescription_tcm` int DEFAULT '0' COMMENT '中医处方数',
   `prescription_western` int DEFAULT '0' COMMENT '西医处方数',
   `inspection_items` int DEFAULT '0' COMMENT '检查项目处方数',
   `treatment_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '治疗费小计',
   `treatment_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合治疗费',
   `treatment_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保治疗费',
   `treatment_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费治疗费',
   `treatment_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费治疗费',
   `diagnostic_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '诊查费小计',
   `diagnostic_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合诊查费',
   `diagnostic_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保诊查费',
   `diagnostic_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费诊查费',
   `diagnostic_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费诊查费',
   `laboratory_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '化验费小计',
   `laboratory_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合化验费',
   `laboratory_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保化验费',
   `laboratory_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费化验费',
   `laboratory_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费化验费',
   `material_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '材料费小计',
   `material_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合材料费',
   `material_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保材料费',
   `material_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费材料费',
   `material_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费材料费',
   `western_medicine_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '西药费小计',
   `western_medicine_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合西药费',
   `western_medicine_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保西药费',
   `western_medicine_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费西药费',
   `western_medicine_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费西药费',
   `inspection_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '检查费小计',
   `inspection_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合检查费',
   `inspection_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保检查费',
   `inspection_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费检查费',
   `inspection_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费检查费',
   `tcm_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '中成药费小计',
   `tcm_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合中成药费',
   `tcm_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保中成药费',
   `tcm_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费中成药费',
   `tcm_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费中成药费',
   `nursing_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '护理费小计',
   `nursing_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合护理费',
   `nursing_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保护理费',
   `nursing_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费护理费',
   `nursing_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费护理费',
   `special_material_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '特殊材料费小计',
   `special_material_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合特殊材料费',
   `special_material_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保特殊材料费',
   `special_material_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费特殊材料费',
   `special_material_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费特殊材料费',
   `surgery_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '手术费小计',
   `surgery_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合手术费',
   `surgery_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保手术费',
   `surgery_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费手术费',
   `surgery_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费手术费',
   `other_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '其它费用小计',
   `other_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合其它费用',
   `other_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保其它费用',
   `other_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费其它费用',
   `other_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费其它费用',
   `bed_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '床位费小计',
   `bed_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合床位费',
   `bed_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保床位费',
   `bed_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费床位费',
   `bed_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费床位费',
   `radiation_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '放射费小计',
   `radiation_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合放射费',
   `radiation_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保放射费',
   `radiation_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费放射费',
   `radiation_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费放射费',
   `registration_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '挂号费小计',
   `registration_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合挂号费',
   `registration_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保挂号费',
   `registration_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费挂号费',
   `registration_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费挂号费',
   `herbal_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '中草药费小计',
   `herbal_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合中草药费',
   `herbal_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保中草药费',
   `herbal_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费中草药费',
   `herbal_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费中草药费',
   `temperature_regulation_fee_total` decimal(12,4) DEFAULT '0.0000' COMMENT '调温费小计',
   `temperature_regulation_fee_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合调温费',
   `temperature_regulation_fee_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保调温费',
   `temperature_regulation_fee_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费调温费',
   `temperature_regulation_fee_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费调温费',
   `total_sum` decimal(12,4) DEFAULT '0.0000' COMMENT '总计',
   `total_sum_nco` decimal(12,4) DEFAULT '0.0000' COMMENT '新农合总计',
   `total_sum_medicare` decimal(12,4) DEFAULT '0.0000' COMMENT '医保总计',
   `total_sum_public` decimal(12,4) DEFAULT '0.0000' COMMENT '公费总计',
   `total_sum_self` decimal(12,4) DEFAULT '0.0000' COMMENT '自费总计',
   PRIMARY KEY (`id`)  -- 设置id字段为主键
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='医生费用明细表'
    """

    # 选择要操作的数据库
    # con.select_db("db_python")

    # 使用游标对象，执行sql
    cursor.execute(sql)

    # cursor.close() 可以省略
except Exception as e:
    print("异常：", e)
finally:
    if con:
        # 关闭连接
        con.close()
